The invention is related to computer systems, and more particularly to query processing and management of result sets.
Many computer systems utilize servers, such as “database servers” to store and maintain information. In a client-server computer system model (or a multi-tiered computer architecture), users that wish to access or modify information at the server are often located at a “client.” To facilitate the explanation of the invention, the terms “database server” and “database client” may be used in this document in place of “server” and “client;” however, the invention is not limited in its applicability to database systems, and indeed, can be utilized in many other types of computer systems.
In client-server systems, users at a database client submit commands to the database server to store, modify, or retrieve data. In response to the user commands, data manipulation or query activities are performed at the database server, with data results returned back to the database client. In networked environments, the database server often performs data manipulation or query commands submitted by remotely located clients. The client may establish a direct connection to the database server over the network, or may establish a connection through one or more intervening system components, such as an application server or transaction processing monitor. In either case, the database server processes the user commands and generates appropriate data outputs to be returned to the client.
A common database function, for example, is to perform data queries using a database query language such as SQL. The database server receives each query and generates a query result set that satisfies the criteria defined by a particular query. The query result set is subsequently transferred to the database client from which the query originated.
A cursor is a handle to a query execution area, e.g., an area in memory in which a parsed structured query language (“SQL”) statement and other information for processing the SQL statement is stored. In many database systems, a cursor is an available resource for accessing or parsing SQL statements embedded within an application. Cursors enable a SQL statement to retrieve rows from a database table into a given result set, which can then be accessed, displayed, or operated upon.
Each cursor has a client side cursor state and a server side cursor state. In server processing there are circumstances when the server side cursor state will go down. For example, when performing load balancing a server will migrate, restart, and/or redirect a session (e.g., a client and/or server session) from one server to another. This can cause an interruption in result set availability. It may be necessary to have access to a result set for the life of a client side cursor.
The present invention is directed to a method, system, and computer program product for suspending a result set and continuing from a suspended result set for a cursor. The rows from a row source are suspended into storage so that when a fetch is requested the data can be sent (continued) from storage and does not depend on the original row source or session to be available. In some embodiment(s) of the invention, suspending and continuing logic is used for cursors. A method of suspending a result set includes determining that a result set is to be suspended, storing a first set of data of the result set in a storage area, and repeating the storing until the required data has been suspended, the storage area being available after the result set is no longer available.
Further details of the embodiments are described below in the detailed description, drawings, and claims.